package server.dao;

/**************************************************************
 *   Filename : TbUserDAO.java
 *   Function : 
 *   Comment :
 *   History : 
 *   @version 1.0
 *   @author �좎룞�쇿뜝�숈삕
 *************************************************************/

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import server.util.PagingBean;
import server.util.ReqParam;
import server.util.StringUtil;
import server.vo.TbProcessVO;
import server.vo.TbProjectVO;
import server.vo.TbUserVO;

public class TbUserDAO {

	/**
	 * Login 泥섇뜝�숈삕
	 * 
	 * @param conn
	 * @param user_id
	 * @return TbUserVO
	 * @throws Exception
	 */
	public TbUserVO login(Connection conn, String user_id) throws Exception {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		TbUserVO tbUserVO = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("\n SELECT user_id, user_nm, user_pwd ");
			sql.append("\n 	  FROM [dbo].[TB_USER] ");
			sql.append("\n  WHERE   ");
			sql.append("\n  	 [user_id] = ?   ");
			

			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, user_id);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				tbUserVO = new TbUserVO();
				tbUserVO.setUser_id(rs.getString("user_id"));
				tbUserVO.setUser_name(rs.getString("user_name"));
				tbUserVO.setUser_pwd(rs.getString("user_pwd"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
		}
		return tbUserVO;
	}

	/**
	 * �좎룞��
	 * 
	 * @param conn
	 * @param u_id
	 * @return TbUserVO
	 * @throws Exception
	 */
	public TbUserVO userInfo(Connection conn, TbUserVO in_tbUserVO) throws Exception {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		TbUserVO tbUserVO = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("\n SELECT user_id, user_name, user_pwd");
			sql.append("\n 	  FROM [dbo].[TB_USER] ");
			sql.append("\n  WHERE   ");
			sql.append("\n  	user_id = ?   ");

			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, in_tbUserVO.getUser_id());
			
			System.out.println("---"+in_tbUserVO.getUser_id());
			
			rs = pstmt.executeQuery();
			if (rs.next()) {
				tbUserVO = new TbUserVO();
				tbUserVO.setUser_id(rs.getString("user_id"));
				tbUserVO.setUser_name(rs.getString("user_name"));
				tbUserVO.setUser_pwd(rs.getString("user_pwd"));
			}
		} catch (Exception e) {

		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
		}
		return tbUserVO;
	}

	/**
	 * �좎룞�쇿뜝�숈삕��
	 * 
	 * @param conn
	 * @param u_id
	 * @return
	 * @throws Exception
	 */
	
	public ArrayList<TbUserVO> list(Connection conn, ReqParam reqParam, PagingBean pagingBean) throws Exception {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<TbUserVO> result = new ArrayList<TbUserVO>();
		
		try {
			ArrayList<Object> params = new ArrayList<Object>();
			StringBuffer sql = new StringBuffer();
			sql.append("\n SELECT  ");
			sql.append("\n 		   [user_id] ");
			sql.append("\n 		  ,[user_nm] ");
			sql.append("\n 		  ,[user_pwd] ");
			sql.append("\n 		  ,[LOGIN_DATE] ");
			sql.append("\n 	FROM(       ");
			sql.append("\n 		SELECT ");
			sql.append("\n 		   [user_id] ");
			sql.append("\n 		  ,[user_name] ");
			sql.append("\n 		  ,[user_pwd] ");
		    sql.append("\n 		  ,[LOGIN_DATE] ");
			sql.append("\n 		FROM [dbo].[TB_USER] ");

			sql.append("\n 	) x ");
			sql.append("\n WHERE x.rn >= ? AND x.rn <= ? ");

			pstmt = conn.prepareStatement(sql.toString());

			params.add(pagingBean.getStartNo());
			params.add(pagingBean.getEndNo());

			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(i + 1, params.get(i));
			}

			rs = pstmt.executeQuery();

			while (rs.next()) {
				TbUserVO tbUserVO = new TbUserVO();
				tbUserVO.setUser_id(rs.getString("user_id"));
				tbUserVO.setUser_name(rs.getString("user_name"));
				tbUserVO.setUser_pwd(rs.getString("user_pwd"));
				tbUserVO.setLogin_date(rs.getTimestamp("login_date"));
								
				result.add(tbUserVO);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
		}
		return result;
	}

	/**
	 * 
	 * @param conn
	 * @param st_pg
	 * @param ed_pg
	 * @return
	 * @throws Exception
	 */

	public long totalCnt(Connection conn, ReqParam reqParam) throws Exception {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		long cnt = 0;
		try {
			ArrayList<Object> params = new ArrayList<Object>();
			StringBuffer sql = new StringBuffer();
			sql.append("\n SELECT count(user_id) cnt ");
			sql.append("\n 	  FROM [dbo].[TB_USER] ");
			if (!"".equals(StringUtil.empty(reqParam.getParam2()))) {
				sql.append("\n     WHERE ").append(reqParam.getParam1()).append(" like  '%'+?+'%' ");
				params.add(reqParam.getParam2());
			}
			pstmt = conn.prepareStatement(sql.toString());

			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(i + 1, params.get(i));
			}
			rs = pstmt.executeQuery();

			rs.next();
			cnt = rs.getLong("cnt");
		} catch (Exception e) {

		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
		}
		return cnt;
	}

	/**
	 * �좎뙃琉꾩삕
	 * 
	 * @param conn
	 * @param u_id
	 * @return
	 * @throws Exception
	 */
	public boolean insert(Connection conn, TbUserVO tbUserVO) throws Exception {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("\n INSERT INTO  [dbo].[TB_USER]");
			sql.append("\n 	(user_id, user_name, user_pwd) ");
			sql.append("\n 	VALUES ");
			sql.append("\n 	(?, ?, ?");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, tbUserVO.getUser_id().trim());
			pstmt.setString(2, tbUserVO.getUser_name());
			pstmt.setString(3, tbUserVO.getUser_pwd().trim());
			

			return pstmt.executeUpdate() > 0 ? true : false;
		} catch (Exception e) {
			return false;
		} finally {
			if (pstmt != null)
				pstmt.close();
		}
	}

	/**
	 * �좎룞�쇿뜝�숈삕
	 * 
	 * @param conn
	 * @param u_id
	 * @return
	 * @throws Exception
	 */
	public boolean modify(Connection conn, TbUserVO tbUserVO) throws Exception {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("\n UPDATE  [dbo].[TB_USER]");
			sql.append("\n 	SET");
			sql.append("\n 		USER_ID = ? ");
			sql.append("\n 	   ,USER_PWD = ? ");
			sql.append("\n 	   ,LOGIN_DATE = GETDATE()  ");
			sql.append("\n 	WHERE  ");
			sql.append("\n 		USER_ID = ? ");
			
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, tbUserVO.getUser_name().trim());
			pstmt.setString(2, tbUserVO.getUser_pwd().trim());
			pstmt.setString(3, tbUserVO.getUser_id());
	
			return pstmt.executeUpdate() > 0 ? true : false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			if (pstmt != null)
				pstmt.close();
		}
	}

	
	
	public boolean delete(Connection conn, TbUserVO tbUserVO) throws Exception {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("\n DELETE  FROM [dbo].[TB_USER]");
			sql.append("\n 	WHERE USER_ID = ?");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, tbUserVO.getUser_id().trim());
			return pstmt.executeUpdate() > 0 ? true : false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			if (pstmt != null)
				pstmt.close();
		}
	}

	public boolean chnagePwd(Connection conn, TbUserVO tbUserVO) throws Exception {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("\n UPDATE  [dbo].[TB_USER]");
			sql.append("\n 	SET");
			sql.append("\n 	    USER_PWD = ? ");
			sql.append("\n 	WHERE  ");
			sql.append("\n 		USER_ID = ? ");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, tbUserVO.getUser_pwd());
			pstmt.setString(2, tbUserVO.getUser_id().trim());
			return pstmt.executeUpdate() > 0 ? true : false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			if (pstmt != null)
				pstmt.close();
		}
	}
	
	
	/**
	 * �좎룞�쇿뜝�숈삕
	 * 
	 * @param conn
	 * @param u_id
	 * @return
	 * @throws Exception
	 */
	public boolean updateLoginInit(Connection conn, TbUserVO tbUserVO) throws Exception {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("\n UPDATE  [dbo].[TB_USER]");
			sql.append("\n 	SET");
			sql.append("\n 	   LOGIN_DATE = GETDATE()  ");
			sql.append("\n 	WHERE  ");
			sql.append("\n 		USER_ID = ? ");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, tbUserVO.getUser_id());

			return pstmt.executeUpdate() > 0 ? true : false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			if (pstmt != null)
				pstmt.close();
		}
	}

	/**
	 * getUserListAll 
	 * @param conn
	 * @return List<TbUserVO> �좎룞�쇿뜝�숈삕�좑옙�좎룞�쇿뜝�숈삕��
	 * @throws SQLException 
	 */
	public List<TbUserVO> getUserListAll(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<TbUserVO> result = new ArrayList<TbUserVO>();
		
		try {
			
			StringBuffer sql = new StringBuffer();
			sql.append("\n SELECT  ");
			sql.append("\n 		   [user_id] ");
			sql.append("\n 		  ,[user_name] ");
			sql.append("\n 		  ,[user_pwd] ");
			sql.append("\n 		  ,[LOGIN_DATE] ");
			sql.append("\n 	FROM [dbo].[TB_USER] ");			

			pstmt = conn.prepareStatement(sql.toString());
			
			rs = pstmt.executeQuery();

			while (rs.next()) {
				TbUserVO tbUserVO = new TbUserVO();
				tbUserVO.setUser_id(rs.getString("user_id"));
				tbUserVO.setUser_name(rs.getString("user_name"));
				tbUserVO.setUser_pwd(rs.getString("user_pwd"));
				tbUserVO.setLogin_date(rs.getTimestamp("login_date"));
								
				result.add(tbUserVO);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
		}
		return result;
	}

	public List<TbUserVO> getUserListByUserName(Connection conn, TbUserVO paramUserVO) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<TbUserVO> result = new ArrayList<TbUserVO>();
		
		try {
			
			StringBuffer sql = new StringBuffer();
			sql.append("\n SELECT  ");
			sql.append("\n 		   [user_id] ");
			sql.append("\n 		  ,[user_name] ");
			sql.append("\n 		  ,[user_pwd] ");
			sql.append("\n 		  ,[LOGIN_DATE] ");
			sql.append("\n 	FROM [dbo].[TB_USER] ");
			sql.append("\n 	WHERE ");
			sql.append("\n 	      user_name like ? ");

			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, "%"+paramUserVO.getUser_name()+"%");
			
			rs = pstmt.executeQuery();

			while (rs.next()) {
				TbUserVO tbUserVO = new TbUserVO();
				tbUserVO.setUser_id(rs.getString("user_id"));
				tbUserVO.setUser_name(rs.getString("user_name"));
				tbUserVO.setUser_pwd(rs.getString("user_pwd"));
				tbUserVO.setLogin_date(rs.getTimestamp("login_date"));
								
				result.add(tbUserVO);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
		}
		return result;
	}

	public List<TbUserVO> getUserListByUserId(Connection conn, TbUserVO paramUserVO) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<TbUserVO> result = new ArrayList<TbUserVO>();
		
		try {
			
			StringBuffer sql = new StringBuffer();
			sql.append("\n SELECT  ");
			sql.append("\n 		   [user_id] ");
			sql.append("\n 		  ,[user_name] ");
			sql.append("\n 		  ,[user_pwd] ");
			sql.append("\n 		  ,[LOGIN_DATE] ");
			sql.append("\n 	FROM [dbo].[TB_USER] ");			
			sql.append("\n 	WHERE ");
			sql.append("\n 	      user_id = ? ");
						
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, paramUserVO.getUser_id());
			
			rs = pstmt.executeQuery();

			while (rs.next()) {
				TbUserVO tbUserVO = new TbUserVO();
				tbUserVO.setUser_id(rs.getString("user_id"));
				tbUserVO.setUser_name(rs.getString("user_name"));
				tbUserVO.setUser_pwd(rs.getString("user_pwd"));
				tbUserVO.setLogin_date(rs.getTimestamp("login_date"));
								
				result.add(tbUserVO);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
		}
		return result; 
	}

	public List<TbUserVO> getUserListByPjtCode(Connection conn,
			TbProjectVO tbPjtVO) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<TbUserVO> result = new ArrayList<TbUserVO>();
		
		try {
			
			StringBuffer sql = new StringBuffer();
			
			sql.append(" select t2.user_id ");
			sql.append(" , t2.user_name ");
			sql.append(" , t2.user_pwd ");
			sql.append(" , t2.login_date ");
			sql.append(" , t1.role ");			
			sql.append(" from dbo.TB_PROJECT_USER t1 ");
			sql.append(" inner join dbo.TB_USER t2 on t1.USER_ID = t2.USER_ID ");
			sql.append(" where t1.PJT_CODE = ? ");
			
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, tbPjtVO.getProject_code());
			
			rs = pstmt.executeQuery();

			while (rs.next()) {
				TbUserVO tbUserVO = new TbUserVO();
				tbUserVO.setUser_id(rs.getString("user_id"));
				tbUserVO.setUser_name(rs.getString("user_name"));
				tbUserVO.setUser_pwd(rs.getString("user_pwd"));
				tbUserVO.setLogin_date(rs.getTimestamp("login_date"));
				tbUserVO.setRole(rs.getString("role"));
								
				result.add(tbUserVO);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
		}
		return result; 
	}

	public boolean registerUserToPjt(Connection conn,TbProcessVO tvProcessVO) throws SQLException {
		PreparedStatement pstmt = null;
		
		StringBuffer sql = new StringBuffer();
		
		sql.append(" insert into dbo.TB_PROJECT_USER (PJT_CODE, USER_ID, ROLE) values (?, ?, ?) ");
		
		pstmt = conn.prepareStatement(sql.toString());
		pstmt.setString(1, tvProcessVO.getPjt_code());		
		pstmt.setString(2, tvProcessVO.getUesr_id());
		pstmt.setString(3, tvProcessVO.getRoll());		
		
		int insertCnt = pstmt.executeUpdate();
		if(insertCnt >0) {
			return true;
		} else {
			return false;
		}		
	}	
	
}
